create or replace function upd_timestamp() returns trigger as
$$
begin
    new.gmt_modified = current_timestamp;
    return new;
end
$$
language plpgsql;

create table mdms_database
(
    id                bigint not null
        constraint mdms_database_pkey
            primary key,
    database_name     varchar(30),
    database_type     varchar(30),
    database_dbname   varchar(20),
    database_host     varchar(100),
    database_port     integer,
    database_user     varchar(30),
    database_password varchar(100),
    gmt_create        timestamp default CURRENT_TIMESTAMP,
    gmt_modified      timestamp default CURRENT_TIMESTAMP
);

alter table mdms_database
    owner to mdms;


create trigger mdms_database before update on mdms_database for each row execute procedure upd_timestamp();


create table mdms_datachange_apply
(
    id              bigint not null
        constraint mdms_datachange_apply_pkey
            primary key,
    apply_user_id   bigint not null,
    apply_user_name varchar(50),
    apply_date      varchar(8),
    apply_time      varchar(6),
    database_id     bigint,
    database_name   varchar(50),
    apply_content   text,
    affect_line_num integer,
    back_content    text,
    apply_status    integer,
    run_status      integer,
    check_user_id   bigint,
    check_user_name varchar(50),
    check_date      varchar(8),
    check_time      varchar(6),
    gmt_create      timestamp default CURRENT_TIMESTAMP,
    gmt_modified    timestamp default CURRENT_TIMESTAMP
);

alter table mdms_datachange_apply
    owner to mdms;

create trigger mdms_datachange_apply before update on mdms_datachange_apply for each row execute procedure upd_timestamp();

create table mdms_sshserver
(
    id           bigint not null
        constraint mdms_sshserver_pkey
            primary key,
    server_name  varchar(30),
    server_host  varchar(100),
    server_port  integer,
    gmt_create   timestamp default CURRENT_TIMESTAMP,
    gmt_modified timestamp default CURRENT_TIMESTAMP
);

alter table mdms_sshserver
    owner to mdms;

create trigger mdms_sshserver before update on mdms_sshserver for each row execute procedure upd_timestamp();

create table mdms_sshserver_user
(
    id              bigint not null
        constraint mdms_sshserver_user_pkey
            primary key,
    server_id       bigint,
    server_user     varchar(30),
    server_password varchar(100),
    gmt_create      timestamp default CURRENT_TIMESTAMP,
    gmt_modified    timestamp default CURRENT_TIMESTAMP
);

alter table mdms_sshserver_user
    owner to mdms;

create unique index uk_server_id_server_user
	on mdms_sshserver_user (server_id, server_user);

create trigger mdms_sshserver_user before update on mdms_sshserver_user for each row execute procedure upd_timestamp();

create table mdms_user
(
    id               bigint not null
        constraint mdms_user_pkey
            primary key,
    user_code        varchar(32),
    user_name        varchar(50),
    user_password    varchar(50),
    user_sex         varchar(1),
    mobile_no        varchar(11),
    mailbox_no       varchar(30),
    user_type        varchar(3),
    user_status      varchar(2),
    pwderr_times     integer,
    lastpwderr_time  varchar(14),
    if_changepwd     varchar(1),
    mfa_flag         varchar(1),
    mfa_secret       varchar(200),
    skin             varchar(20),
    bind_third_uniid varchar(100),
    rec_crt_oprid    varchar(32),
    crt_oprid_org_id varchar(15),
    rec_upt_oprid    varchar(32),
    gmt_create       timestamp default CURRENT_TIMESTAMP,
    gmt_modified     timestamp default CURRENT_TIMESTAMP
);

alter table mdms_user
    owner to mdms;

create unique index uk_thirduniid
	on mdms_user (bind_third_uniid);

create trigger mdms_user before update on mdms_user for each row execute procedure upd_timestamp();

create table mdms_user_commonsql
(
    id           bigint not null
        constraint mdms_user_commonsql_pkey
            primary key,
    user_id      bigint,
    name         varchar(50),
    common_sql   text,
    gmt_create   timestamp default CURRENT_TIMESTAMP,
    gmt_modified timestamp default CURRENT_TIMESTAMP
);

alter table mdms_user_commonsql
    owner to mdms;

create trigger mdms_user_commonsql before update on mdms_user_commonsql for each row execute procedure upd_timestamp();

create table mdms_user_database
(
    id           bigint not null
        constraint mdms_user_database_pkey
            primary key,
    server_id    bigint,
    user_id      bigint,
    database_id  bigint,
    gmt_create   timestamp default CURRENT_TIMESTAMP,
    gmt_modified timestamp default CURRENT_TIMESTAMP
);

alter table mdms_user_database
    owner to mdms;

create unique index uk_user_id_database_id
	on mdms_user_database (user_id, database_id);

create trigger mdms_user_database before update on mdms_user_database for each row execute procedure upd_timestamp();

create table mdms_user_log
(
    id              bigint not null
        constraint mdms_user_log_pkey
            primary key,
    apply_user_id   bigint,
    apply_user_name varchar(50),
    apply_id        bigint,
    check_user_id   bigint,
    check_user_name varchar(50),
    database_id     bigint,
    database_name   varchar(50),
    host_ipport     varchar(100),
    run_sql         text,
    affect_line_num integer,
    gmt_create      timestamp default CURRENT_TIMESTAMP,
    gmt_modified    timestamp default CURRENT_TIMESTAMP
);

alter table mdms_user_log
    owner to mdms;

create index idx_apply_user_id
	on mdms_user_log (apply_user_id);

create trigger mdms_user_log before update on mdms_user_log for each row execute procedure upd_timestamp();

create table mdms_user_privilige
(
    id           bigint not null
        constraint mdms_user_privilige_pkey
            primary key,
    if_add       varchar(1),
    if_update    varchar(1),
    if_delete    varchar(1),
    gmt_create   timestamp default CURRENT_TIMESTAMP,
    gmt_modified timestamp default CURRENT_TIMESTAMP
);

alter table mdms_user_privilige
    owner to mdms;

create trigger mdms_user_privilige before update on mdms_user_privilige for each row execute procedure upd_timestamp();

create table mdms_user_sshserver
(
    id             bigint not null
        constraint mdms_user_sshserver_pkey
            primary key,
    user_id        bigint,
    server_id      bigint,
    server_user_id bigint,
    gmt_create     timestamp default CURRENT_TIMESTAMP,
    gmt_modified   timestamp default CURRENT_TIMESTAMP
);

alter table mdms_user_sshserver
    owner to mdms;

create unique index uk_user_id_server_user_id
	on mdms_user_sshserver (user_id, server_user_id);

create trigger mdms_user_sshserver before update on mdms_user_sshserver for each row execute procedure upd_timestamp();
